package model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import controller.Cliente;

/**
*
* @author Juliane
*/
public class ClienteDAO {

    private Connection con;
    private PreparedStatement stmtInserir;
    private PreparedStatement stmtAtualizar;
    private PreparedStatement stmtDeletar;
    private PreparedStatement stmtConsultar;    
    private PreparedStatement stmtListar;
    private PreparedStatement stmtBuscarCpf;
    private PreparedStatement stmtBuscarCpf2;
    private PreparedStatement stmtBuscarRg;
    private PreparedStatement stmtBuscarRg2;
    private PreparedStatement stmtBuscarNome;
    private PreparedStatement stmtBuscarSobrenome; 
    private PreparedStatement stmtVincularConta;
    private PreparedStatement stmtSemConta;

    public ClienteDAO() throws Exception {
        con = ConFactory.getConnection();
        stmtInserir = con.prepareStatement("INSERT INTO cliente(cpf, rg, nome, sobrenome, endereco) VALUES(?,?,?,?,?)");
        stmtAtualizar = con.prepareStatement("UPDATE cliente SET rg = ?, nome = ?, sobrenome = ?, endereco = ? WHERE cpf = ?");
        stmtDeletar = con.prepareStatement ("DELETE FROM cliente WHERE cpf = ?");
        stmtConsultar = con.prepareStatement("SELECT * FROM cliente WHERE cpf = ?");
        stmtListar = con.prepareStatement("SELECT * FROM cliente");
        stmtBuscarCpf = con.prepareStatement("SELECT * FROM cliente WHERE cpf = ? ");
        stmtBuscarCpf2 = con.prepareStatement("SELECT * FROM cliente WHERE cpf = ? AND nconta IS NULL");
        stmtBuscarRg = con.prepareStatement("SELECT * FROM cliente WHERE rg = ? ");
        stmtBuscarRg2 = con.prepareStatement("SELECT * FROM cliente WHERE rg = ? AND nconta IS NULL");
        stmtBuscarNome = con.prepareStatement("SELECT * FROM cliente WHERE nome LIKE '% ? %'");
        stmtBuscarSobrenome = con.prepareStatement("SELECT * FROM cliente WHERE sobrenome LIKE '%?%'");
        stmtVincularConta = con.prepareStatement("UPDATE cliente SET nconta = ? WHERE cpf = ?");
        stmtSemConta = con.prepareStatement("SELECT * FROM cliente WHERE nconta IS NULL");
    }

    public ClienteDAO(Connection con) throws Exception {
        this.con = con;
        stmtInserir = con.prepareStatement("INSERT INTO cliente(cpf, rg, nome, sobrenome, endereco) VALUES(?,?,?,?,?)");
        stmtAtualizar = con.prepareStatement("UPDATE cliente SET rg = ?, nome = ?, sobrenome = ?, endereco = ? WHERE cpf = ?");
        stmtDeletar = con.prepareStatement ("DELETE * FROM cliente WHERE cpf = ?");
        stmtConsultar = con.prepareStatement("SELECT * FROM cliente WHERE cpf = ?");
        stmtListar = con.prepareStatement("SELECT * FROM cliente");
        stmtBuscarCpf = con.prepareStatement("SELECT * FROM cliente WHERE cpf = ? ");
        stmtBuscarCpf2 = con.prepareStatement("SELECT * FROM cliente WHERE cpf = ? AND nconta IS NULL");
        stmtBuscarRg = con.prepareStatement("SELECT * FROM cliente WHERE rg = ? ");
        stmtBuscarRg2 = con.prepareStatement("SELECT * FROM cliente WHERE rg = ? AND nconta IS NULL");
        stmtBuscarNome = con.prepareStatement("SELECT * FROM cliente WHERE nome LIKE '%?%' ");
        stmtBuscarSobrenome = con.prepareStatement("SELECT * FROM cliente WHERE sobrenome LIKE '%?%'");
        stmtVincularConta = con.prepareStatement("UPDATE cliente SET nconta = ? WHERE cpf = ?");
        stmtSemConta = con.prepareStatement("SELECT * FROM cliente WHERE nconta IS NULL");
    }

    public void inserirCliente(Cliente cliente) throws Exception {
        stmtInserir.setString(1, cliente.getCpf());
        stmtInserir.setString(2, cliente.getRg());
        stmtInserir.setString(3, cliente.getNome());
        stmtInserir.setString(4, cliente.getSobrenome());
        stmtInserir.setString(5, cliente.getEndereco());
        stmtInserir.executeUpdate();
    }
    
    public void atualizarCliente(Cliente cliente) throws Exception{
    	stmtAtualizar.setString(1, cliente.getRg());
    	stmtAtualizar.setString(2, cliente.getNome());
    	stmtAtualizar.setString(3, cliente.getSobrenome());
    	stmtAtualizar.setString(4, cliente.getEndereco());
        stmtAtualizar.setString(5, cliente.getCpf());
    	stmtAtualizar.executeUpdate();
    }
    
    public void deletarCliente(String cpf) throws Exception{
        stmtDeletar.setString(1, cpf);
    	stmtDeletar.executeUpdate();
    }

    public Cliente consultarCliente(String cpf) throws Exception {
        stmtConsultar.setString(1, cpf);
        ResultSet rs = stmtConsultar.executeQuery();
        rs.next();
        Cliente clienteBuscado = new Cliente(rs.getString("nome"), rs.getString("sobrenome"), rs.getString("rg"), rs.getString("cpf"), rs.getString("endereco"));
        return clienteBuscado;
    }

    public List<Cliente> listarCliente() throws Exception {
       ResultSet rs = stmtListar.executeQuery();
       List<Cliente> listaCliente = new ArrayList();
       while(rs.next()){
          Cliente cliente = new Cliente(rs.getString("nome"), rs.getString("sobrenome"), rs.getString("rg"), rs.getString("cpf"), rs.getString("endereco"));
          listaCliente.add(cliente);
       }
       return listaCliente;
    }
    
    public List<Cliente> listarClienteSConta() throws Exception{
        ResultSet rs = stmtSemConta.executeQuery();
        List<Cliente> listaCliente = new ArrayList();
        while(rs.next()){
            Cliente cliente = new Cliente(rs.getString("nome"), rs.getString("sobrenome"), rs.getString("rg"), rs.getString("cpf"), rs.getString("endereco"));
            listaCliente.add(cliente);
        }
        return listaCliente;
    }
    
   public List<Cliente> busca(String busca, int parametro) throws Exception{
    	List<Cliente> listaCliente = new ArrayList();
        switch (parametro){
    		//cpf
    		case 0: {
    			stmtBuscarCpf.setString(1, busca);
    			ResultSet rs = stmtBuscarCpf.executeQuery();
    			if (rs.next()){
                            Cliente clienteBuscado = new Cliente(rs.getString("nome"), rs.getString("sobrenome"), rs.getString("rg"), rs.getString("cpf"), rs.getString("endereco"));
                            listaCliente.add(clienteBuscado);
                        }
                        return listaCliente;
    		}
    		//rg
    		case 1: {
    			stmtBuscarRg.setString(1, busca);
    			ResultSet rs = stmtBuscarRg.executeQuery();
    			if(rs.next()){
                            Cliente clienteBuscado = new Cliente(rs.getString("nome"), rs.getString("sobrenome"), rs.getString("rg"), rs.getString("cpf"), rs.getString("endereco"));
                            listaCliente.add(clienteBuscado);
                        }
                        return listaCliente;
    		}
    		//nome
    		case 2: {
                        String str = "SELECT * FROM cliente WHERE nome LIKE '%" + busca + "%'";
                        //stmtBuscarNome = con.prepareStatement(str);
    			//stmtBuscarNome.setString(1, busca);
    			ResultSet rs = stmtBuscarNome.executeQuery(str);
    			while(rs.next()){
                            Cliente clienteBuscado = new Cliente(rs.getString("nome"), rs.getString("sobrenome"), rs.getString("rg"), rs.getString("cpf"), rs.getString("endereco"));
                            listaCliente.add(clienteBuscado);
                        }
                        return listaCliente;                    
    		}
    		//sobrenome
                case 3: {
                        String str = "SELECT * FROM cliente WHERE sobrenome LIKE '%" + busca + "%'";
                        //stmtBuscarNome = con.prepareStatement(str);
    			//stmtBuscarNome.setString(1, busca);    			
    			//stmtBuscarSobrenome.setString(1, busca);
    			ResultSet rs = stmtBuscarSobrenome.executeQuery(str);
    			while(rs.next()){
                            Cliente clienteBuscado = new Cliente(rs.getString("nome"), rs.getString("sobrenome"), rs.getString("rg"), rs.getString("cpf"), rs.getString("endereco"));
                            listaCliente.add(clienteBuscado);
                        }
                        return listaCliente;
    		}
                default: return null;
    	}
    }
   
    public List<Cliente> busca2(String busca, int parametro) throws Exception{
    	List<Cliente> listaCliente = new ArrayList();
        switch (parametro){
    		//cpf
    		case 0: {
    			stmtBuscarCpf2.setString(1, busca);
    			ResultSet rs = stmtBuscarCpf2.executeQuery();
    			if (rs.next()){
                            Cliente clienteBuscado = new Cliente(rs.getString("nome"), rs.getString("sobrenome"), rs.getString("rg"), rs.getString("cpf"), rs.getString("endereco"));
                            listaCliente.add(clienteBuscado);
                        }
                        return listaCliente;
    		}
    		//rg
    		case 1: {
    			stmtBuscarRg2.setString(1, busca);
    			ResultSet rs = stmtBuscarRg2.executeQuery();
    			if(rs.next()){
                            Cliente clienteBuscado = new Cliente(rs.getString("nome"), rs.getString("sobrenome"), rs.getString("rg"), rs.getString("cpf"), rs.getString("endereco"));
                            listaCliente.add(clienteBuscado);
                        }
                        return listaCliente;
    		}
    		//nome
    		case 2: {
                        String str = "SELECT * FROM cliente WHERE nome LIKE '%" + busca + "%' AND nconta IS NULL";
                        //stmtBuscarNome = con.prepareStatement(str);
    			//stmtBuscarNome.setString(1, busca);
    			ResultSet rs = stmtBuscarNome.executeQuery(str);
    			while(rs.next()){
                            Cliente clienteBuscado = new Cliente(rs.getString("nome"), rs.getString("sobrenome"), rs.getString("rg"), rs.getString("cpf"), rs.getString("endereco"));
                            listaCliente.add(clienteBuscado);
                        }
                        return listaCliente;                    
    		}
    		//sobrenome
                case 3: {
                        String str = "SELECT * FROM cliente WHERE sobrenome LIKE '%" + busca + "%' AND nconta IS NULL";
                        //stmtBuscarNome = con.prepareStatement(str);
    			//stmtBuscarNome.setString(1, busca);    			
    			//stmtBuscarSobrenome.setString(1, busca);
    			ResultSet rs = stmtBuscarSobrenome.executeQuery(str);
    			while(rs.next()){
                            Cliente clienteBuscado = new Cliente(rs.getString("nome"), rs.getString("sobrenome"), rs.getString("rg"), rs.getString("cpf"), rs.getString("endereco"));
                            listaCliente.add(clienteBuscado);
                        }
                        return listaCliente;
    		}
                default: return null;
    	}
    }
   
   public void vinculaCliente(String cpf, int nconta) throws Exception{
       stmtVincularConta.setInt(1, nconta);
       stmtVincularConta.setString(2, cpf);
       stmtVincularConta.executeUpdate();          
   }
    
    @Override
    public void finalize() throws Throwable{
        super.finalize();
        stmtInserir.close();
        stmtConsultar.close();
        stmtListar.close();
        stmtDeletar.close();
        stmtBuscarCpf.close();
        stmtBuscarCpf2.close();
        stmtBuscarRg.close();
        stmtBuscarRg2.close();
        stmtBuscarNome.close();
        stmtBuscarSobrenome.close();
        stmtVincularConta.close();
        con.close();
    }
}

